﻿using System;
using System.Data.SQLite;
using System.Configuration;
using System.IO;

namespace DBStorageFromADS
{
    class ADSDBSQLite3
    {
        //public static string dbPath = "Data Source =" + Environment.CurrentDirectory + "/Database.db3";
        //public static string dbPath = ConfigurationManager.AppSettings["dbPath"];// ConfigurationSettings.AppSettings["dbPath"];
        public static string dbDirPath = System.IO.Directory.GetCurrentDirectory()+ @"\DataBase\";
        public static int N = Convert.ToInt32(ConfigurationManager.AppSettings["Number"]);
        public static string dbPath = @"Data Source="+dbDirPath + @"\Database.db3";

        public static void CreateTables()
        {
            SQLiteConnection conn = null;
            if(!Directory.Exists(dbDirPath))
            {
                Directory.CreateDirectory(dbDirPath);
            }
            conn = new SQLiteConnection(@dbPath);//创建数据库实例，指定文件位置
            conn.Open();//打开数据库，若文件不存在会自动创建
            string sql = GenerateCreateTableSQL("RESULT_L", ADSDBSQLite3.N) 
                + GenerateCreateTableSQL("RESULT_R", ADSDBSQLite3.N) 
                + GenerateCreateTableSQL("SJCG", ADSDBSQLite3.N);
            SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn);
            cmdCreateTable.ExecuteNonQuery();//如果表不存在，创建数据表  
            conn.Close();
        }

        public static string GenerateCreateTableSQL(string table,int num) 
        {
            string temp = " ";
            string s = "";
            for (int i = 1; i <= num; i++)
            {
                s = Convert.ToString(i);
                if (i==num)
                {
                    temp = temp + table + s + " varchar(20));";
                }else
                    temp = temp + table + s + " varchar(20),";
            }
            return "CREATE TABLE IF NOT EXISTS " +table+ "(time varchar(20),"+ temp;
        }

        public static string GenerateInsertSQL(string table,string[] values) 
        {
            string temp = " ";
            int len = values.Length;
            int lentemp = len -1;
            for (int i = 0; i < len;i++ )
            {
                if (i == lentemp)
                {
                    temp = temp + "@" + values[i] + ");";
                }
                else
                {
                    temp = temp + "@" + values[i] + ",";
                }
            }
            return "insert into " +table+ " values(@time,"+temp;
        }

        public static string GenerateInsertSQL(string table, int num)
        {
            string temp = " ";
            string s = "";
            for (int i = 1; i <= num; i++)
            {
                s = Convert.ToString(i);
                if (i == num)
                {
                    temp = temp + "@" +  table +s + " );";
                }
                else
                {
                    temp = temp + "@" + table + s + " ,";
                }
            }
            return "insert into " + table + " values(@time," + temp;
        }


    }
}
